"""
Case Type   : Compatibility
Case Name   : 数值类型导入
Create At   : 2023/07/19
Owner       : n@ningyali
Description :
        1.建表，字段整数类型
        2.建表，字段任意精度型
        3.建表，字段序列整型
        4.建表，字段浮点类型
        5.导入数据
        6.查询表数据
        7.清理环境
Expect      :
        1.成功
        2.成功
        3.成功
        4.成功
        5.成功
        6.成功
        7.成功
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class LoadData0090(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.constant = Constant()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0090'
        self.db_name = f'db_{self.case_lable}'
        self.t_name_1 = f't_{self.case_lable}_1'
        self.t_name_2 = f't_{self.case_lable}_2'
        self.t_name_3 = f't_{self.case_lable}_3'
        self.t_name_4 = f't_{self.case_lable}_4'
        self.file_path = os.path.join('/home', self.pri_user.ssh_user)
        self.cols1 = 'col1 tinyint,col2 smallint,col3 integer,' \
                     'col4 binary_integer,col5 bigint'
        self.cols2 = 'col1 numeric(12,2),col2 decimal(12,3),col3 number(12,4)'
        self.cols3 = 'col1 smallserial,col2 serial,col3 bigserial'
        self.cols4 = 'col1 real,col2 float4,col3 double precision,' \
                     'col4 float8,col5 float(3),col6 binary_double,' \
                     'col7 dec(10,4),col8 integer'

        text1 = '-----创建数据文件；expect:创建成功-----'
        self.log.info(text1)
        self.data1 = "127\t32767\t2147483647\t2147483647\t9223372036854775807"
        self.data2 = "1.131456\t1.131456\t1.131456"
        self.data3 = "1\t2\t3"
        self.data4 = "1.131456\t1.131456\t1.131456\t1.131456\t1.131456\t" \
                     "1.131456\t1.131456\t1.131456"
        res = self.load_data.create_data_file(self.pri_user, self.case_lable,
                                              self.data1, self.data2,
                                              self.data3, self.data4)
        self.assertTrue(res, '执行失败:' + text1)

        text2 = '-----创建兼容B库；expect:创建成功-----'
        self.log.info(text2)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text2)

    def test_load(self):
        text = '-----step1：建表，字段整数类型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name_1, self.cols1,
                                          detail=True)
        self.assertIn('CREATE TABLE', res, '执行失败:' + text)

        text = '-----step2：建表，字段任意精度型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name_2, self.cols2)
        self.assertTrue(res, '执行失败:' + text)

        text = '-----step3：建表，字段序列整型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name_3, self.cols3)
        self.assertTrue(res, '执行失败:' + text)

        text = '-----step4：建表，字段浮点类型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name_4, self.cols4)
        self.assertTrue(res, '执行失败:' + text)

        text = '-----step5：导入数据；expect：成功-----'
        self.log.info(text)
        f_path_1 = os.path.join(self.file_path, f'{self.case_lable}_1.txt')
        f_path_2 = os.path.join(self.file_path, f'{self.case_lable}_2.txt')
        f_path_3 = os.path.join(self.file_path, f'{self.case_lable}_3.txt')
        f_path_4 = os.path.join(self.file_path, f'{self.case_lable}_4.txt')
        sql = f"load data infile '{f_path_1}' into table {self.t_name_1};" \
            f"load data infile '{f_path_2}' into table {self.t_name_2};" \
            f"load data infile '{f_path_3}' into table {self.t_name_3};" \
            f"load data infile '{f_path_4}' into table {self.t_name_4};"
        res = ''
        for s in sql.split(';')[:-1]:
            res += self.pri_sh.execut_db_sql(s, dbname=self.db_name)
        self.assertEqual(res.count('COPY 1'), 4, '执行失败' + text)

        text = '-----step6：查询表数据；expect：成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name_1};" \
            f"select * from {self.t_name_2};" \
            f"select * from {self.t_name_3};" \
            f"select * from {self.t_name_4};"
        sql_list = [i for i in sql.split(';') if i]
        expects = [[['127', '32767', '2147483647', '2147483647',
                    '9223372036854775807']],
                   [['1.13', '1.131', '1.1315']],
                   [['1', '2', '3']],
                   [['1.13146', '1.13146', '1.131456', '1.131456',
                    '1.13146', '1.131456', '1.1315', '1']]]
        for s in sql_list:
            res = self.pri_sh.execut_db_sql(s, dbname=self.db_name)
            rows, _ = self.common.parse_sql_query_res(res)
            self.assertEqual(rows, expects[sql_list.index(s)], '执行失败' + text)

    def tearDown(self):
        self.log.info('-----step7：清理环境；expect：成功-----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       os.path.join(self.file_path,
                                                    f"{self.case_lable}*"))
        self.assertTrue(res, '执行失败:清理环境')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
